﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using TimeTabling.Core;
using System.Data.SqlClient;
using System.Data;
using TimeTabling.Core.Contracts;

namespace TimeTabling.DAL
{
    public class CenterDAO : GenericDAO<Center>, ICenterDAO
    {

        public CenterDAO(string connectionString) 
            :base(connectionString)
        {            
        }

        /*
        public void Add(Center center)
        {
            string command ="Insert into Center Values(@Id, @Name, @Address)";
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                SqlCommand sqlCommand = new SqlCommand(command, sqlConnection);
                sqlCommand.Parameters.Add("@Id", center.Id);
                sqlCommand.Parameters.Add("@Name", center.Name);
                sqlCommand.Parameters.Add("@Address", center.Address);
                try
                {
                    sqlConnection.Open();
                    sqlCommand.ExecuteNonQuery();
                }
                finally
                {
                    if (sqlConnection.State != ConnectionState.Closed)
                    {
                        sqlConnection.Close();
                    }
                }
            }
        }

        public Center Get(Guid id)
        {
            Center result = null;
            string command = @"Select Id, Name, Address 
                               From Center 
                               Where Id = @Id";
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                SqlCommand sqlCommand = new SqlCommand(command, sqlConnection);
                sqlCommand.Parameters.AddWithValue("@Id", id);
                sqlConnection.Open();
                using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
                {
                    if (sqlDataReader.HasRows)
                    {
                        sqlDataReader.Read();
                        result = new Center()
                        {
                            Id = (Guid)sqlDataReader["Id"],
                            Name = (string)sqlDataReader["Name"],
                            Address = (string)sqlDataReader["Address"]
                        };
                        sqlDataReader.Close();
                    }
                }
                sqlConnection.Close();
            }
            return result;
        }

        public IList<Center> GetAll()
        {
            IList<Center> resultList = new List<Center>();            
            string command = @"Select Id, Name, Address 
                               From Center";
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                SqlCommand sqlCommand = new SqlCommand(command, sqlConnection);
                sqlConnection.Open();
                using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
                {
                    if (sqlDataReader.HasRows)
                    {
                        while (sqlDataReader.Read())
                        {
                            resultList.Add(new Center()
                            {
                                Id = (Guid)sqlDataReader["Id"],
                                Name = (string)sqlDataReader["Name"],
                                Address = (string)sqlDataReader["Address"]
                            });
                        }                        
                        sqlDataReader.Close();
                    }
                }
                sqlConnection.Close();
            }
            return resultList;
        }


        public void Update(Center center)
        {
            string command = "Update Center Set Name=@Name, Address=@Address where Id=@Id";
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                SqlCommand sqlCommand = new SqlCommand(command, sqlConnection);
                sqlCommand.Parameters.Add("@Id", center.Id);
                sqlCommand.Parameters.Add("@Name", center.Name);
                sqlCommand.Parameters.Add("@Address", center.Address);
                try
                {
                    sqlConnection.Open();
                    sqlCommand.ExecuteNonQuery();
                }
                finally
                {
                    if (sqlConnection.State != ConnectionState.Closed)
                    {
                        sqlConnection.Close();
                    }
                }
            }
        }

        public int Count()
        {
            string command = "select count(id) From Center";
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                SqlCommand sqlCommand = new SqlCommand(command, sqlConnection);
                try
                {
                    sqlConnection.Open();
                    return (int)sqlCommand.ExecuteScalar();
                }
                finally
                {
                    if (sqlConnection.State != ConnectionState.Closed)
                    {
                        sqlConnection.Close();
                    }
                }
            }
        }

        public void Remove(Center center)
        {
            string command = "Delete from Center where Id=@Id";
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                SqlCommand sqlCommand = new SqlCommand(command, sqlConnection);
                sqlCommand.Parameters.Add("@Id", center.Id);
                try
                {
                    sqlConnection.Open();
                    sqlCommand.ExecuteNonQuery();
                }
                finally
                {
                    if (sqlConnection.State != ConnectionState.Closed)
                    {
                        sqlConnection.Close();
                    }
                }
            }
        }
        */
    }
}
